﻿Imports System.Windows.Forms
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Microsoft.Win32
Imports System.IO
Imports ESBD.Search_Results

Public Class Modify_Record
    Public Original_File As String
    Public Edit_AtchFile As String = "" 'The whole path
    Public Edit_Atch_FileName As String = "" 'The name of the file and extension
    Public Edit_Atch_File_Ext As String = "" 'Just the extension of the file

    Private Sub BTN_Modify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTN_Modify.Click
        'Ask for password
        If EditRec_PW <> "" Then
            If Admin_PW_Entered = "Y" Or EditRec_PW_Entered = "Y" Then
                GoTo Edit_Rec
            Else
                Dim Passwd As String
                Passwd = InputBox("Please enter the password to Edit Records or the Administrator password.", "Edit Records Password")
                If Passwd = Admin_PW Then
                    Admin_PW_Entered = "Y"
                    EditRec_PW_Entered = "Y"
                    DelRec_PW_Entered = "Y"
                    GoTo Edit_Rec
                ElseIf Passwd = EditRec_PW Then
                    EditRec_PW_Entered = "Y"
                    GoTo Edit_rec
                ElseIf Passwd = "" Then
                    GoTo Finish
                Else
                    MsgBox("Sorry the password you entered did not match the password on file." + vbCrLf + "Please check spelling and capitalization and try again.", MsgBoxStyle.Information)
                    GoTo Finish
                End If
            End If
        Else
            GoTo Edit_Rec
        End If

Edit_Rec:
        'Edit Record
        If Original_File = "" And TB_AttachFile.Text = "" Then
            ESBD_Form.OpenSQLConnection()
            Dim conn As New SqlConnection(csb.ToString())
            Try
                cmd.Connection = conn
                conn.Open()

                'Edit Record
                cmd.CommandText = "Update ESBD_Main set Project_Name = '" + cmb_ProjName.Text + "', Requested_Date = '" + TB_ReqDate.Text + "', Completion_Date = '" + TB_CompDate.Text + "', Ship_Date = '" + TB_ShipDate.Text + "', Build_Quantity = '" + TB_BuildQty.Text + "', Build_Type = '" + TB_BuildType.Text + "', BOM_Rev = '" + TB_BOMRev.Text + "', PCB_Rev = '" + TB_PCBRev.Text + "', PCB_ZNN = '" + TB_PCBZNN.Text + "', PCB_Manufacturer = '" + cmb_PCBManu.Text + "', PCB_Assembler = '" + cmb_PCBAssm.Text + "', LED_Specification = '" + TB_LEDSpec.Text + "', Special_Build_Instruction = '" + TB_BuildIns.Text + "', ""Customer"" = '" + cmb_Customer.Text + "', ""Status"" = '" + cmb_Status.Text + "', Comments = '" + TB_Comments.Text + "' where Job_ID = '" + TB_JobID.Text + "'"
                cmd.ExecuteScalar()
                conn.Close()

                MsgBox("Record has been successfully edited.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Information)
                Se_Re.Dispose()
                Search_Form.Btn_Search.PerformClick()
                Me.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
                conn.Close()
                Me.Cursor = Cursors.Default
            End Try
        Else
            If Original_File <> TB_AttachFile.Text Then
                If TB_AttachFile.Text = "" Then
                    'Original file is to be deleted and a new file has not been chosen
                    'Delete Original File
                    Try
                        ESBD_Form.OpenSQLConnection()
                        Dim connection As New SqlConnection(csb.ToString())
                        cmd.Connection = connection
                        connection.Open()
                        cmd.CommandText = "Delete From Attachments_TB where Filename = '" + Original_File + "'"
                        cmd.ExecuteScalar()

                        'Update main table to show blank shipping record along with any other changes
                        cmd.CommandText = "Update ESBD_Main set Project_Name = '" + cmb_ProjName.Text + "', Requested_Date = '" + TB_ReqDate.Text + "', Completion_Date = '" + TB_CompDate.Text + "', Ship_Date = '" + TB_ShipDate.Text + "', Build_Quantity = '" + TB_BuildQty.Text + "', Build_Type = '" + TB_BuildType.Text + "', BOM_Rev = '" + TB_BOMRev.Text + "', PCB_Rev = '" + TB_PCBRev.Text + "', PCB_ZNN = '" + TB_PCBZNN.Text + "', PCB_Manufacturer = '" + cmb_PCBManu.Text + "', PCB_Assembler = '" + cmb_PCBAssm.Text + "', LED_Specification = '" + TB_LEDSpec.Text + "', Special_Build_Instruction = '" + TB_BuildIns.Text + "', ""Customer"" = '" + cmb_Customer.Text + "', ""Status"" = '" + cmb_Status.Text + "', Comments = '" + TB_Comments.Text + "', Shipping_Record = '" + Edit_Atch_FileName + "' where Job_ID = '" + TB_JobID.Text + "'"
                        cmd.ExecuteScalar()
                        connection.Close()

                        MsgBox("Record has been successfully edited.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Information)
                        Se_Re.Dispose()
                        Search_Form.Btn_Search.PerformClick()
                        Me.Close()
                    Catch ex As Exception
                        Dim connection As New SqlConnection(csb.ToString())
                        connection.Close()
                        MsgBox(ex.Message)
                        Me.Cursor = Cursors.Default
                    End Try
                ElseIf Original_File = "" Then
                    'No file was originally attached. User has decided to attach a file
                    'Get Filename, path and extension
                    Edit_File_Segments()

                    'Upload file to Database
                    Dim fs As New FileStream(Edit_AtchFile, FileMode.Open, FileAccess.Read)
                    Dim br As New BinaryReader(fs)
                    Dim numBytes As Integer = New FileInfo(Edit_AtchFile).Length
                    Dim buff As Byte() = br.ReadBytes(numBytes)

                    Try
                        ESBD_Form.OpenSQLConnection()
                        Dim connection As New SqlConnection(csb.ToString())
                        cmd.Connection = connection
                        connection.Open()

                        cmd.CommandText = "INSERT INTO Attachments_TB ( [FileName], [Extension], [Content] ) VALUES('" + Edit_Atch_FileName + "', '" + Edit_Atch_File_Ext + "', @Content);"
                        cmd.Parameters.Add(New SqlParameter("@Content", buff))

                        cmd.ExecuteNonQuery()
                        numBytes = 0
                        buff = Nothing
                        cmd.Parameters.Clear()
                        connection.Close()

                        connection.Open()
                        'Update main table to show new shipping record along with any other changes
                        cmd.CommandText = "Update ESBD_Main set Project_Name = '" + cmb_ProjName.Text + "', Requested_Date = '" + TB_ReqDate.Text + "', Completion_Date = '" + TB_CompDate.Text + "', Ship_Date = '" + TB_ShipDate.Text + "', Build_Quantity = '" + TB_BuildQty.Text + "', Build_Type = '" + TB_BuildType.Text + "', BOM_Rev = '" + TB_BOMRev.Text + "', PCB_Rev = '" + TB_PCBRev.Text + "', PCB_ZNN = '" + TB_PCBZNN.Text + "', PCB_Manufacturer = '" + cmb_PCBManu.Text + "', PCB_Assembler = '" + cmb_PCBAssm.Text + "', LED_Specification = '" + TB_LEDSpec.Text + "', Special_Build_Instruction = '" + TB_BuildIns.Text + "', ""Customer"" = '" + cmb_Customer.Text + "', ""Status"" = '" + cmb_Status.Text + "', Comments = '" + TB_Comments.Text + "', Shipping_Record = '" + Edit_Atch_FileName + "' where Job_ID = '" + TB_JobID.Text + "'"
                        cmd.ExecuteScalar()
                        connection.Close()

                        MsgBox("Record has been successfully edited.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Information)
                        Se_Re.Dispose()
                        Search_Form.Btn_Search.PerformClick()
                        Me.Close()
                    Catch ex As Exception
                        Dim connection As New SqlConnection(csb.ToString())
                        cmd.Connection = connection
                        connection.Open()

                        'Revert Attach file changes since new file was not located
                        cmd.CommandText = "Update ESBD_Main set Project_Name = '" + cmb_ProjName.Text + "', Requested_Date = '" + TB_ReqDate.Text + "', Completion_Date = '" + TB_CompDate.Text + "', Ship_Date = '" + TB_ShipDate.Text + "', Build_Quantity = '" + TB_BuildQty.Text + "', Build_Type = '" + TB_BuildType.Text + "', BOM_Rev = '" + TB_BOMRev.Text + "', PCB_Rev = '" + TB_PCBRev.Text + "', PCB_ZNN = '" + TB_PCBZNN.Text + "', PCB_Manufacturer = '" + cmb_PCBManu.Text + "', PCB_Assembler = '" + cmb_PCBAssm.Text + "', LED_Specification = '" + TB_LEDSpec.Text + "', Special_Build_Instruction = '" + TB_BuildIns.Text + "', ""Customer"" = '" + cmb_Customer.Text + "', ""Status"" = '" + cmb_Status.Text + "', Comments = '" + TB_Comments.Text + "', Shipping_Record = '" + Original_File + "' where Job_ID = '" + TB_JobID.Text + "'"
                        cmd.ExecuteScalar()
                        connection.Close()
                    End Try
                Else
                    'There was a previous file and user has selected a new file
                    'Get Filename, path and extension of new file
                    Edit_File_Segments()

                    'Upload file to Database
                    Dim fs As New FileStream(Edit_AtchFile, FileMode.Open, FileAccess.Read)
                    Dim br As New BinaryReader(fs)
                    Dim numBytes As Integer = New FileInfo(Edit_AtchFile).Length
                    Dim buff As Byte() = br.ReadBytes(numBytes)

                    Try
                        ESBD_Form.OpenSQLConnection()
                        Dim connection As New SqlConnection(csb.ToString())
                        cmd.Connection = connection
                        connection.Open()

                        cmd.CommandText = "INSERT INTO Attachments_TB ( [FileName], [Extension], [Content] ) VALUES('" + Edit_Atch_FileName + "', '" + Edit_Atch_File_Ext + "', @Content);"
                        cmd.Parameters.Add(New SqlParameter("@Content", buff))

                        cmd.ExecuteNonQuery()
                        numBytes = 0
                        buff = Nothing
                        cmd.Parameters.Clear()
                        connection.Close()

                        connection.Open()
                        'Update main table to show new shipping record along with any other changes
                        cmd.CommandText = "Update ESBD_Main set Project_Name = '" + cmb_ProjName.Text + "', Requested_Date = '" + TB_ReqDate.Text + "', Completion_Date = '" + TB_CompDate.Text + "', Ship_Date = '" + TB_ShipDate.Text + "', Build_Quantity = '" + TB_BuildQty.Text + "', Build_Type = '" + TB_BuildType.Text + "', BOM_Rev = '" + TB_BOMRev.Text + "', PCB_Rev = '" + TB_PCBRev.Text + "', PCB_ZNN = '" + TB_PCBZNN.Text + "', PCB_Manufacturer = '" + cmb_PCBManu.Text + "', PCB_Assembler = '" + cmb_PCBAssm.Text + "', LED_Specification = '" + TB_LEDSpec.Text + "', Special_Build_Instruction = '" + TB_BuildIns.Text + "', ""Customer"" = '" + cmb_Customer.Text + "', ""Status"" = '" + cmb_Status.Text + "', Comments = '" + TB_Comments.Text + "', Shipping_Record = '" + Edit_Atch_FileName + "' where Job_ID = '" + TB_JobID.Text + "'"
                        cmd.ExecuteScalar()
                        connection.Close()

                        'Delete Original File attachment
                        connection.Open()
                        cmd.CommandText = "Delete From Attachments_TB where Filename = '" + Original_File + "'"
                        cmd.ExecuteScalar()
                        connection.Close()

                        MsgBox("Record has been successfully edited.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Information)
                        Se_Re.Dispose()
                        Search_Form.Btn_Search.PerformClick()
                        Me.Close()
                    Catch ex As Exception
                        Dim connection As New SqlConnection(csb.ToString())
                        cmd.Connection = connection
                        connection.Open()

                        'Revert Attach file changes since new file was not located
                        cmd.CommandText = "Update ESBD_Main set Project_Name = '" + cmb_ProjName.Text + "', Requested_Date = '" + TB_ReqDate.Text + "', Completion_Date = '" + TB_CompDate.Text + "', Ship_Date = '" + TB_ShipDate.Text + "', Build_Quantity = '" + TB_BuildQty.Text + "', Build_Type = '" + TB_BuildType.Text + "', BOM_Rev = '" + TB_BOMRev.Text + "', PCB_Rev = '" + TB_PCBRev.Text + "', PCB_ZNN = '" + TB_PCBZNN.Text + "', PCB_Manufacturer = '" + cmb_PCBManu.Text + "', PCB_Assembler = '" + cmb_PCBAssm.Text + "', LED_Specification = '" + TB_LEDSpec.Text + "', Special_Build_Instruction = '" + TB_BuildIns.Text + "', ""Customer"" = '" + cmb_Customer.Text + "', ""Status"" = '" + cmb_Status.Text + "', Comments = '" + TB_Comments.Text + "', Shipping_Record = '" + Original_File + "' where Job_ID = '" + TB_JobID.Text + "'"
                        cmd.ExecuteScalar()
                        connection.Close()
                    End Try
                End If
            Else
                'Original file = TB_AtchFile and neither are blank (user did not change attached file)
                ESBD_Form.OpenSQLConnection()
                Dim conn As New SqlConnection(csb.ToString())
                Try
                    cmd.Connection = conn
                    conn.Open()

                    'Edit Record
                    cmd.CommandText = "Update ESBD_Main set Project_Name = '" + cmb_ProjName.Text + "', Requested_Date = '" + TB_ReqDate.Text + "', Completion_Date = '" + TB_CompDate.Text + "', Ship_Date = '" + TB_ShipDate.Text + "', Build_Quantity = '" + TB_BuildQty.Text + "', Build_Type = '" + TB_BuildType.Text + "', BOM_Rev = '" + TB_BOMRev.Text + "', PCB_Rev = '" + TB_PCBRev.Text + "', PCB_ZNN = '" + TB_PCBZNN.Text + "', PCB_Manufacturer = '" + cmb_PCBManu.Text + "', PCB_Assembler = '" + cmb_PCBAssm.Text + "', LED_Specification = '" + TB_LEDSpec.Text + "', Special_Build_Instruction = '" + TB_BuildIns.Text + "', ""Customer"" = '" + cmb_Customer.Text + "', ""Status"" = '" + cmb_Status.Text + "', Comments = '" + TB_Comments.Text + "' where Job_ID = '" + TB_JobID.Text + "'"
                    cmd.ExecuteScalar()
                    conn.Close()

                    MsgBox("Record has been successfully edited.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Information)
                    Se_Re.Dispose()
                    Search_Form.Btn_Search.PerformClick()
                    Me.Close()
                Catch ex As Exception
                    MsgBox(ex.Message)
                    conn.Close()
                    Me.Cursor = Cursors.Default
                End Try
            End If
        End If
Finish:
    End Sub

    Private Sub Edit_File_Segments()
        Dim AttachFile_Path As String() 'String() is a string array
        Dim FileName As String
        Dim ary_FileExtension As String()
        Dim Extension As String

        AttachFile_Path = Edit_AtchFile.Split(New Char() {"\"c}) 'New Char() is an array. Here we are saying the string array words, will hold each piece of the attach filepath string that will be split by the \ character

        For i = 0 To UBound(AttachFile_Path)

            If i = UBound(AttachFile_Path) Then

                FileName = AttachFile_Path(i)
                Edit_Atch_FileName = FileName
                ary_FileExtension = FileName.Split(New Char() {"."c})

                For j = 0 To UBound(ary_FileExtension)

                    If j = UBound(ary_FileExtension) Then
                        'Assign Extension of file
                        Extension = ary_FileExtension(j)
                        Edit_Atch_File_Ext = Extension
                    End If

                Next

            End If

        Next

    End Sub

    Private Sub BTN_BrowseFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTN_BrowseFile.Click
        'User will be prompted to remove current attached file
        'If yes, file will be removed and then user will be prompted to attach another file.
        '===================================================================================
        If TB_AttachFile.Text <> "" Then
            Dim prompt_DelFile As DialogResult

            prompt_DelFile = MsgBox("Remove the currently attached file from this record?", MsgBoxStyle.OkCancel Or MsgBoxStyle.Question)

            If prompt_DelFile = DialogResult.OK Then
                GoTo Delfile
            Else
                GoTo Finish
            End If

Delfile:
            TB_AttachFile.Text = ""
            'Prompt to attach different file
            Dim prompt_AtchFile As DialogResult

            prompt_AtchFile = MsgBox("Current file will be removed when this record is edited." + vbCrLf + vbCrLf + "Attach a different file?", MsgBoxStyle.YesNo Or MsgBoxStyle.Question)

            If prompt_AtchFile = DialogResult.Yes Then
                GoTo AtchFile
            Else
                GoTo Finish
            End If
        Else
AtchFile:
            'Display file open dialog to let user choose file
            OpenFD1.FileName = ""
            OpenFD1.Filter = "Supported File Types|*.txt; *.xls; *.xlsx; *.jpeg; *.gif; *.jpg; *.bmp; *.pdf; *.doc; *.docx; *.htm; *.html"
            Dim FD_Result As Integer = OpenFD1.ShowDialog()

            If FD_Result = DialogResult.Cancel Then
                GoTo Finish
            End If

            'Assigns Full path and name of file to the variable AttachFile
            Edit_AtchFile = OpenFD1.FileName
            TB_AttachFile.Text = Edit_AtchFile
            MsgBox("Changes will take effect when this record is edited.", MsgBoxStyle.OkOnly Or MsgBoxStyle.Information)
        End If

Finish:
    End Sub

    Private Sub Modify_Record_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Original_File = TB_AttachFile.Text

        'Populate Comboboxes
        Try
            'Clear ComboBoxes
            cmb_Customer.Items.Clear()
            'cmb_AttachFile.Items.Clear()
            cmb_PCBAssm.Items.Clear()
            cmb_PCBManu.Items.Clear()
            cmb_ProjName.Items.Clear()
            cmb_Status.Items.Clear()

            'Refresh ComboBox
            ESBD_Form.OpenSQLConnection()
            Dim connection As New SqlConnection(csb.ToString())
            cmd.Connection = connection
            connection.Open()

            'Use reader to popluate comboboxes
            cmd.CommandText = "Select ""Name"" from ""CUST_TB"""
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                cmb_Customer.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            cmd.CommandText = "Select ""Name"" from PCBAssm_TB"
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                cmb_PCBAssm.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            cmd.CommandText = "Select ""Name"" from PCBManu_TB"
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                cmb_PCBManu.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            cmd.CommandText = "Select ""PJ_Name"" from Project_TB"
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                cmb_ProjName.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            cmd.CommandText = "Select ""Name"" from Status_TB"
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                cmb_Status.Items.Add(dr.GetString(0))
            Loop
            dr.Close()
            connection.Close()

            'Sort the Combobox
            cmb_Customer.Sorted = True
            cmb_PCBAssm.Sorted = True
            cmb_PCBManu.Sorted = True
            cmb_ProjName.Sorted = True
            cmb_Status.Sorted = True
        Catch ex As Exception
            Dim connection As New SqlConnection(csb.ToString())
            connection.Close()
            MsgBox(ex.Message)
        End Try
    End Sub

#Region "ComboBoxes Click Events"
#Region "Show Dropdown items on click"
    Private Sub cmb_CustName_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_Customer.Click
        cmb_Customer.DroppedDown = True
    End Sub

    Private Sub cmb_PCBAssm_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_PCBAssm.Click
        cmb_PCBAssm.DroppedDown = True
    End Sub

    Private Sub cmb_PCBManu_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_PCBManu.Click
        cmb_PCBManu.DroppedDown = True
    End Sub

    Private Sub cmb_ProjName_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_ProjName.Click
        cmb_ProjName.DroppedDown = True
    End Sub

    Private Sub cmb_Status_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_Status.Click
        cmb_Status.DroppedDown = True
    End Sub
#End Region

#Region "Prevent Users from typing into comboboxes"
    Private Sub cmb_ProjName_keypress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cmb_ProjName.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmb_PCBAssm_keypress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cmb_PCBAssm.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmb_PCBManu_keypress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cmb_PCBManu.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmb_Status_keypress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cmb_Status.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmb_CustName_keypress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles cmb_Customer.KeyPress
        e.Handled = True
    End Sub
#End Region
#End Region
End Class